﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            DataSet ds = (DataSet)dataGridView1.DataSource;
            MessageBox.Show(ds.Tables[0].Rows[e.RowIndex][0] + "," + ds.Tables[0].Rows[e.RowIndex][1]);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            String bookno = this.textBox1.Text;
            String bookname = this.textBox2.Text;

            //获得数据库连接字符串
            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            if (bookno == "" || bookname == "")
                MessageBox.Show("书本编号和书名不能为空！");
            else
            {
                int test = testnull(bookno, connection);
                if (test == 0)//没有这编号
                    addbook(bookno, bookname, connection);
                else
                    MessageBox.Show("这个编号已经有了,不能重复！");
            }
        }
        private int testnull(String bookno,SqlConnection connection)
        {
            //connection.Open();

            String sql = "select BOOKNO from BOOK where BOOKNO='" + bookno + "'";
            SqlCommand command = new SqlCommand(sql, connection);
            int num = Convert.ToInt32(command.ExecuteScalar());
            if(num != 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }

        private void addbook(String bookno, String bookname,SqlConnection connection)
        {
            String sql = String.Format("insert into BOOK (BOOKNO,BOOKNAME) values ('{0}','{1}')", bookno, bookname);
            SqlCommand command = new SqlCommand(sql, connection);
            //command.ExecuteScalar();
            int effect = command.ExecuteNonQuery();
            if (effect == 1)
                MessageBox.Show("成功插入！");
            else
                MessageBox.Show("插入失败！");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            String bookno = this.textBox1.Text;
            String bookname = this.textBox2.Text;

            //获得数据库连接字符串
            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            if (bookno == "" || bookname == "")
                MessageBox.Show("书本编号和书名不能为空！");
            else
            {
                int findbook2 = testnull(bookno, connection);
                if (findbook2 == 1)//有这本书
                {
                    //找有没有外借
                    int findborrowbook2 = findborrowbook(bookno, bookname, connection);
                    if (findborrowbook2 == 1)//这本书在外借，暂时不能删除
                        MessageBox.Show("这本书正在借阅，先还书再修改信息！");
                    else
                    {
                        xiugaibook(bookno, bookname, connection);
                    }
                }
                else
                    MessageBox.Show("没这本书");
            }
 
            connection.Close();
        }
        private void xiugaibook(String bookno,String bookname,SqlConnection connection)
        {
            String sql = "update BOOK set BOOKNAME='"+bookname+"' where BOOKNO='"+bookno+"'";
            SqlCommand command = new SqlCommand(sql, connection);
            command.ExecuteScalar();
            int effect = command.ExecuteNonQuery();
            if (effect == 1)
                MessageBox.Show("成功修改！");
            else
                MessageBox.Show("修改失败！");  
        }

        private void button5_Click(object sender, EventArgs e)
        {
            String readno = this.textBox3.Text;
            String readname = this.textBox4.Text;

            //获得数据库连接字符串
            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            if (readno == "" || readname == "")//如果两个其中一个为空
            {
                MessageBox.Show("读者编号和读者姓名不能为空！");
            }
            else
            {
                int test = testnull2(readno, connection);
                if (test == 0)//没有这编号
                    addread(readno, readname, connection);
                else
                    MessageBox.Show("这个编号已经有了,不能重复！");
            }

            connection.Close();
        }
        private int testnull2(String readno, SqlConnection connection)
        {
            String sql = "select READNO from READER where READNO='" + readno + "'";
            SqlCommand command = new SqlCommand(sql, connection);
            int num = Convert.ToInt32(command.ExecuteScalar());
            if (num != 0)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }
        private void addread(String readno, String readname,SqlConnection connection)
        {
            String sql = String.Format("insert into READER (READNO,READNAME) values ('{0}','{1}')", readno, readname);
            SqlCommand command = new SqlCommand(sql, connection);
            //command.ExecuteScalar();
            int effect = command.ExecuteNonQuery();
            if (effect == 1)
                MessageBox.Show("成功插入！");
            else
                MessageBox.Show("插入失败！");
        }

        private void button7_Click(object sender, EventArgs e)
        {
             String readno = this.textBox3.Text;
            String readname = this.textBox4.Text;

            //获得数据库连接字符串
            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            if (readno == "" || readname == "")
                MessageBox.Show("要删除的读者的编号和姓名不能为空！");
            else
            {
                //找这个人存不存在
                int findman2 = findman(readno, readname, connection);
                if(findman2 == 1)//这个人存在
                {
                    int findborrow2 = findborrow(readno, readname, connection);
                    if(findborrow2 == 1)//这个人有借书，暂时不能删除
                        MessageBox.Show("此读者有正在借阅的书，先还书再删除信息！");
                    else
                    {
                        deleteread(readno, readname, connection);
                    }
                }
                else//这个人不存在
                {
                    MessageBox.Show("没有这个编号或读者姓名错误，请检查！");
                }
            }

            connection.Close();
        }

        private int findman(String readno, String readname, SqlConnection connection)
        {
            String sql = "select READNO from READER where READNO=" + readno + " and READNAME='" + readname + "'";
            SqlCommand command = new SqlCommand(sql, connection);
            int effect = Convert.ToInt32(command.ExecuteScalar());
            if (effect != 0)
                return 1;
            else
                return 0;
        }

        private int findborrow(String readno, String readname, SqlConnection connection)
        {
            String sql = "select REPAY from THING where READNO=" + readno;
            SqlCommand command = new SqlCommand(sql, connection);
            int effect = Convert.ToInt32(command.ExecuteScalar());
            if (effect != 0)//有借书情况，不能删除这个人
                return 1;
            else
                return 0;
        }

        private void deleteread(String readno, String readname, SqlConnection connection)
        {
            String sql = "delete from READER where READNO=" + readno;
            SqlCommand command = new SqlCommand(sql, connection);
            int effect = command.ExecuteNonQuery();
            if (effect == 1)
                MessageBox.Show("删除成功！");
            else
                MessageBox.Show("删除失败！");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            String bookno = this.textBox1.Text;
            String bookname = this.textBox2.Text;

            //获得数据库连接字符串
            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            if (bookno == "" || bookname == "")
                MessageBox.Show("要删除的书本的编号和书名不能为空！");
            else
            {
                //找这本书存不存在
                int findbook2;
                String sql = "select BOOKNO from BOOK where BOOKNO=" + bookno + " and BOOKNAME='" + bookname + "'";
                SqlCommand command = new SqlCommand(sql, connection);
                int effect = Convert.ToInt32(command.ExecuteScalar());
                if (effect != 0)
                    findbook2 = 1;
                else
                    findbook2 = 0;
                if (findbook2 == 1)//这本书存在
                {
                    int findborrowbook2 = findborrowbook(bookno, bookname, connection);
                    if (findborrowbook2 == 1)//这本书在外借，暂时不能删除
                        MessageBox.Show("这本书正在借阅，先还书再删除信息！");
                    else
                    {
                        deletebook(bookno, bookname, connection);
                    }
                }
                else//这本书不存在
                {
                    MessageBox.Show("没有这个编号或书名错误，请检查！");
                }
            }

            connection.Close();
        }

        private int findbook(String bookno, String bookname, SqlConnection connection)
        {
            //connection.Open();

            String sql = "select BOOKNO from BOOK where BOOKNO=" + bookno + " and BOOKNAME='" + bookname + "'";
            SqlCommand command = new SqlCommand(sql, connection);
            int effect = Convert.ToInt32(command.ExecuteScalar());
            if (effect != 0)
                return 1;
            else
                return 0;
        }

        private int findborrowbook(String bookno, String bookname, SqlConnection connection)
        {
            //connection.Open();

            String sql = "select REPAY from THING where BOOKNO=" + bookno;
            SqlCommand command = new SqlCommand(sql, connection);
            int effect = Convert.ToInt32(command.ExecuteScalar());
            if (effect != 0)//有借书情况，不能删除本书
                return 1;
            else
                return 0;
        }

        private void deletebook(String bookno, String bookname, SqlConnection connection)
        {
            String sql = "delete from BOOK where BOOKNO=" + bookno + " and BOOKNAME='"+bookname+"'";
            SqlCommand command = new SqlCommand(sql, connection);
            int effect = command.ExecuteNonQuery();
            if (effect == 1)
                MessageBox.Show("删除成功！");
            else
                MessageBox.Show("删除失败！");
        }

        private void button4_Click(object sender, EventArgs e)
        {
            //获得数据库连接字符串
            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            String sql = "select * from BOOK";//定义查询语句
            SqlCommand command = new SqlCommand(sql, connection);
            int num = Convert.ToInt32(command.ExecuteScalar());
            SqlDataAdapter da = new SqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            da.SelectCommand = command;
            da.Fill(ds, "BOOK");
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "BOOK";

            connection.Close();
        }

        private void button8_Click(object sender, EventArgs e)
        {
            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            String sql = "select * from READER";//定义查询语句
            SqlCommand command = new SqlCommand(sql, connection);
            int num = Convert.ToInt32(command.ExecuteScalar());
            SqlDataAdapter da = new SqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            da.SelectCommand = command;
            da.Fill(ds, "READER");
            dataGridView2.DataSource = ds;
            dataGridView2.DataMember = "READER";

            connection.Close();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            String readno = this.textBox3.Text;
            String readname = this.textBox4.Text;

            //获得数据库连接字符串
            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            if (readno == "" || readname == "")
                MessageBox.Show("读者编号和姓名不能为空！");
            else
            {
                int findman2 = testnull2(readno, connection);
                if (findman2 == 1)//这个人存在
                {
                    int findborrow2 = findborrow(readno, readname, connection);
                    if (findborrow2 == 1)//这个人有借书，暂时不能删除
                        MessageBox.Show("此读者有正在借阅的书，先还书再删除信息！");
                    else
                    {
                        xiugairead(readno, readname, connection);
                    }
                }
                else
                    MessageBox.Show("没这个人");
            }

            connection.Close();
        }

        private void xiugairead(String readno, String readname, SqlConnection connection)
        {
            String sql = "update READER set READNAME='" + readname + "' where READNO='" + readno + "'";
            SqlCommand command = new SqlCommand(sql, connection);
            command.ExecuteScalar();
            int effect = command.ExecuteNonQuery();
            if (effect == 1)
                MessageBox.Show("成功修改！");
            else
                MessageBox.Show("修改失败！");
        }

        private void groupBox2_Enter(object sender, EventArgs e)
        {
           
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            button4_Click(sender, e);
            button8_Click(sender, e);
            button11_Click(sender, e);
        }

        private void button11_Click(object sender, EventArgs e)
        {
            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            String sql = "select * from THING";//定义查询语句
            SqlCommand command = new SqlCommand(sql, connection);
            int num = Convert.ToInt32(command.ExecuteScalar());
            SqlDataAdapter da = new SqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            da.SelectCommand = command;
            da.Fill(ds, "THING");
            dataGridView3.DataSource = ds;
            dataGridView3.DataMember = "THING";

            connection.Close();
        }

        private void button9_Click(object sender, EventArgs e)
        {
            String bookno = this.textBox5.Text;
            String readno = this.textBox6.Text;
            String date = this.textBox7.Text;

            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            if(bookno == "" || readno == "" || date == "")
            {
                MessageBox.Show("书本编号、读者编号、借/还日期不能为空！");
            }
            else
            {
                //查询这本书和这个人是否存在
                int test = testnull(bookno, connection);
                int test2 = testnull2(readno, connection);
                if (test != 0 && test2 != 0)
                {
                    jieshu(bookno, readno, date, connection);//借书
                }
                else
                    MessageBox.Show("没有登记这个编号的书或者没有登记这个编号的读者！");
            }

            connection.Close();
        }

        private void jieshu(String bookno, String readno, String date, SqlConnection connection)
        {
            String sql = String.Format("insert into THING (BOOKNO,READNO,BORROWTIME) values ('{0}','{1}','{2}')", bookno, readno, date);
            SqlCommand command = new SqlCommand(sql, connection);
            //command.ExecuteScalar();
            int effect = command.ExecuteNonQuery();
            if (effect == 1)
                MessageBox.Show("成功登记！");
            else
                MessageBox.Show("登记失败！");
        }

        private void button10_Click(object sender, EventArgs e)
        {
            String bookno = this.textBox5.Text;
            String readno = this.textBox6.Text;
            String date = this.textBox7.Text;

            String conn = "Data Source=阿尔杰塔的小跟;Initial Catalog=tushuguan;Integrated Security=True";
            //sqlConnection对象
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();

            if (bookno == "" || readno == "" || date == "")
            {
                MessageBox.Show("书本编号、读者编号、借/还日期不能为空！");
            }
            else
            {
                //查询这本书和这个人是否存在
                int test = testnull(bookno, connection);
                int test2 = testnull2(readno, connection);
                if (test != 0 && test2 != 0)
                {
                    int test1 = testborrow(bookno, readno, connection);
                    if (test1 != 0)
                    {
                        //这本书被这个人借过
                        huanshu(bookno, readno, date, connection);//还书
                    }
                    else
                        MessageBox.Show("这本书没被这个人借过！");
                }
                else
                    MessageBox.Show("没有登记这个编号的书或者没有登记这个编号的读者！");
            }

            connection.Close();
        }

        private int testborrow(String bookno, String readno, SqlConnection connection)
        {
            String sql = "select BOOKNO from THING where BOOKNO="+bookno+" and READNO="+readno;
            SqlCommand command = new SqlCommand(sql, connection);
            int effect = Convert.ToInt32(command.ExecuteScalar());
            if (effect != 0)
                return 1;
            else
                return 0;
        }

        private void huanshu(String bookno, String readno, String date, SqlConnection connection)
        {
            String sql = "update THING set REPAY='"+date+"' where BOOKNO="+bookno+" and READNO="+readno;
            SqlCommand command = new SqlCommand(sql, connection);
            int effect = command.ExecuteNonQuery();
            if (effect != 0)
                MessageBox.Show("登记成功！");
            else
                MessageBox.Show("登记失败！");
        }

        private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            DataSet ds = (DataSet)dataGridView2.DataSource;
            MessageBox.Show(ds.Tables[0].Rows[e.RowIndex][0] + "," + ds.Tables[0].Rows[e.RowIndex][1]);
        }

        private void dataGridView3_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            DataSet ds = (DataSet)dataGridView3.DataSource;
            MessageBox.Show(ds.Tables[0].Rows[e.RowIndex][0] + "," + ds.Tables[0].Rows[e.RowIndex][1] + "," + ds.Tables[0].Rows[e.RowIndex][2] + "," + ds.Tables[0].Rows[e.RowIndex][3]);
        }
    }
}
